CREATE TABLE TipoUsuario (
  idTipoUsuario INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  descricao VARCHAR(30) NOT NULL,
  PRIMARY KEY(idTipoUsuario)
);

CREATE TABLE texto (
  idtexto INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  titulo VARCHAR(80) NOT NULL,
  subtitulo VARCHAR(200) NOT NULL,
  texto TEXT NOT NULL,
  PRIMARY KEY(idtexto)
);

CREATE TABLE TipoRecurso (
  idTipoRecurso INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  descricao VARCHAR(30) NOT NULL,
  PRIMARY KEY(idTipoRecurso)
);

CREATE TABLE usuario (
  idUsuario INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  idTipoUsuario INTEGER UNSIGNED NOT NULL,
  nome VARCHAR(50) NOT NULL,
  login VARCHAR(30) NOT NULL,
  senha VARCHAR(10) NOT NULL,
  PRIMARY KEY(idUsuario),
  FOREIGN KEY(idTipoUsuario)
    REFERENCES TipoUsuario(idTipoUsuario)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE forum (
  idforum INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  idUsuario INTEGER UNSIGNED NOT NULL,
  assunto VARCHAR(50) NOT NULL,
  topico TEXT NULL,
  dtInsercao DATE NULL,
  PRIMARY KEY(idforum),
  FOREIGN KEY(idUsuario)
    REFERENCES usuario(idUsuario)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE comentarios (
  idcomentarios INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  idUsuario INTEGER UNSIGNED NOT NULL,
  idforum INTEGER UNSIGNED NOT NULL,
  comentario TEXT NOT NULL,
  dtComentario DATE NOT NULL,
  PRIMARY KEY(idcomentarios),
  FOREIGN KEY(idforum)
    REFERENCES forum(idforum)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(idUsuario)
    REFERENCES usuario(idUsuario)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE Recurso (
  idRecurso INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  idtexto INTEGER UNSIGNED NOT NULL,
  idTipoRecurso INTEGER UNSIGNED NOT NULL,
  descricao VARCHAR(40) NOT NULL,
  url VARCHAR(120) NOT NULL,
  PRIMARY KEY(idRecurso),
  FOREIGN KEY(idTipoRecurso)
    REFERENCES TipoRecurso(idTipoRecurso)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
  FOREIGN KEY(idtexto)
    REFERENCES texto(idtexto)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE Questoes (
  idQuestoes INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  idRecurso INTEGER UNSIGNED NULL,
  questao TEXT NOT NULL,
  PRIMARY KEY(idQuestoes),
  FOREIGN KEY(idRecurso)
    REFERENCES Recurso(idRecurso)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE itemQuestao (
  iditemQuestao INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  idQuestoes INTEGER UNSIGNED NOT NULL,
  alternativa TEXT NOT NULL,
  correta BOOL NOT NULL,
  PRIMARY KEY(iditemQuestao, idQuestoes),
  FOREIGN KEY(idQuestoes)
    REFERENCES Questoes(idQuestoes)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);


